﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
//using System.Data.Sql;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;
using NFLDriver;

namespace DataAccessCommon
{
    public class DataAccessCommonClass
    {
        string myConnectionString = "Data Source=localhost;Initial Catalog=NFLData;Integrated Security=True";
        //SqlConnection myConn;
        //OleDbConnection myConn;
        //SqlDataReader myReader;
        //OleDbDataReader myReader;
        public DataAccessCommonClass()
        {
          // myConn = new SqlConnection(ConnectionString);
        }

        public User getUser(string userEmail)
        {
            SqlConnection myConn = null;
            SqlDataReader myReader = null;

            
            User tempUser = null;
            try
            {
            tempUser = new User();
            myConn = new SqlConnection(myConnectionString);
            SqlCommand getUser = new SqlCommand("GetUser", myConn);
            getUser.CommandType = CommandType.StoredProcedure;
            SqlParameter email = getUser.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
            //getUser.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
            email.Direction = ParameterDirection.Input;
            email.Value = userEmail;


            myConn.Open();
            myReader = getUser.ExecuteReader();

            while (myReader.Read())
            {
                if (myReader["FirstName"] != null)
                {
                    tempUser.FirstName = myReader["FirstName"].ToString();
                    tempUser.LastName = myReader["LastName"].ToString();
                    tempUser.Email = myReader["Email"].ToString();
                    tempUser.Password = myReader["Password"].ToString();
                }
                else
                {
                    tempUser.FirstName = null;
                    tempUser.LastName = null;
                    tempUser.Email = null;
                    tempUser.Password = null;
                }
            }
            myReader.Close();
            myConn.Close();

            
            }
            catch (Exception e)
            {
                MessageBox.Show("Exception at DataAccessCommon.getUser()\n" + e.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
               
            }
            if (myConn != null)
            {
                myConn.Close();
            }
            if (myReader != null)
            {
                myReader.Close();
            }

            


            return tempUser;
        }

        public User getSelectedTeams(string email, string password)
        {
            SqlConnection myConn = null;
            SqlDataReader myReader = null;
            User tempUser = null;

            Team[] tempTeams = new Team[7];
            for (int i = 0; i < 7; i++)
            {
                tempTeams[i] = new Team();
            }

            try
            {
                myConn = new SqlConnection(myConnectionString);
                SqlCommand getUserSelectedTeams = new SqlCommand("GetSelectedTeams", myConn);
                getUserSelectedTeams.CommandType = CommandType.StoredProcedure;

                SqlParameter userEmail = getUserSelectedTeams.Parameters.Add("@email", SqlDbType.NVarChar, 50);
                userEmail.Direction = ParameterDirection.Input;
                userEmail.Value = email;

                SqlParameter userPassword = getUserSelectedTeams.Parameters.Add("@password", SqlDbType.NVarChar, 50);
                userPassword.Direction = ParameterDirection.Input;
                userPassword.Value = password;

                myConn.Open();
                string[] tempIdHolder = new string[7];

                myReader = getUserSelectedTeams.ExecuteReader();
                
                while (myReader.Read())
                {
                    if (myReader["Team1ID"] != null)
                    {
                        tempIdHolder[0] = myReader["Team1ID"].ToString();
                        tempIdHolder[1] = myReader["Team2ID"].ToString();
                        tempIdHolder[2] = myReader["Team3ID"].ToString();
                        tempIdHolder[3] = myReader["Team4ID"].ToString();
                        tempIdHolder[4] = myReader["Team5ID"].ToString();
                        tempIdHolder[5] = myReader["Team6ID"].ToString();
                        tempIdHolder[6] = myReader["Team7ID"].ToString();
                    }
                    else
                    {
                        tempIdHolder[0] = null;
                        tempIdHolder[1] = null;
                        tempIdHolder[2] = null;
                        tempIdHolder[3] = null;
                        tempIdHolder[4] = null;
                        tempIdHolder[5] = null;
                        tempIdHolder[6] = null;
                    }

                    
                }
                if (tempIdHolder[0] != null)
                {
                    Guid tempGuid;
                    //string tempString;
                    for (int j = 0; j < 7; j++)
                    {
                        //tempString = tempIdHolder[j];
                        if (Guid.TryParse(tempIdHolder[j], out tempGuid))
                        {
                            tempTeams[j].teamId = tempGuid;
                            tempUser.setSelectedTeam(j, tempTeams[j]);
                        }
                        else
                        {
                            MessageBox.Show("Invalid Guid at dataAccessCommon.getUserSelectedTeams\n" + tempIdHolder[j], "Invalid Guid", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            tempUser = null;
                        }

                    }
                }
                else
                {
                    for (int k = 0; k < 7; k++)
                    {
                        tempUser.setSelectedTeam(k,new Team());
                        tempUser.getSelectedTeam(k).teamId= Guid.Empty;
                        tempUser.getSelectedTeam(k).teamName = "";
                    }
                }
                myConn.Close();
                myReader.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Exception at DataAccessCommon.getUser()\n" + e.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            if (myConn != null)
            {
                myConn.Close();
            }
            if (myReader != null)
            {
                myReader.Close();
            }
            

            return tempUser;
        }

        public Teams getTeams()
        {
            SqlConnection myConn = null;
            SqlDataReader myReader = null;
            Teams tempTeams = new Teams();
            for(int i=0; i<32;i++)
            {
                tempTeams.setTeam(i,new Team());
            }
            

            try
            {
                myConn = new SqlConnection(myConnectionString);
                SqlCommand getTeams = new SqlCommand("GetTeams", myConn);
                getTeams.CommandType = CommandType.StoredProcedure;
                       

                myConn.Open();

                myReader = getTeams.ExecuteReader();
                string[,] tempHolder = new string [32,3];
                int j=0;
                while (myReader.Read())
                {
                    tempHolder[j,0] = myReader["TeamID"].ToString();
                    tempHolder[j,1] = myReader["TeamName"].ToString();
                    tempHolder[j,2] = myReader["TeamColumn"].ToString();
                    j++;
                    
                }
                
                Guid tempGuid;
                for(int k=0; k<32; k++)
                {
                    if(Guid.TryParse(tempHolder[k,0], out tempGuid))
                    {
                        tempTeams.getTeam(k).teamId=tempGuid;
                        tempTeams.getTeam(k).teamName=tempHolder[k,1];
                        tempTeams.getTeam(k).teamColumn= int.Parse(tempHolder[k,2]);
                    }
                    else
                    {
                        MessageBox.Show("Invalid Guid at dataAccessCommon.getTeams\n" + tempHolder[k,0], "Invalid Guid", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //tempTeams = null;
                    }
                }

                
            myConn.Close();
            myReader.Close();   

            }
            catch (Exception e)
            {
                MessageBox.Show("Exception at DataAccessCommon.getUser()\n" + e.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            if (myConn != null)
            {
                myConn.Close();
            }
            if (myReader != null)
            {
                myReader.Close();
            }



            return tempTeams;
        }

        public Teams getGetMatchResultsAll(Teams tempTeams)
        {
            SqlConnection myConn = null;
            SqlDataReader myReader = null;
            
            
            

            try
            {
                myConn = new SqlConnection(myConnectionString);
                SqlCommand getUserSelectedTeams = new SqlCommand("GetMatchResultsAll", myConn);
                getUserSelectedTeams.CommandType = CommandType.StoredProcedure;
                       

                myConn.Open();

                myReader = getUserSelectedTeams.ExecuteReader();
                string[,] tempHolder = new string [32,20];
                int j=0;
                while (myReader.Read())
                {
                    /*
                    tempHolder[j, 0] = myReader["fkTeamID"].ToString();
                    tempHolder[j, 1] = myReader["Year"].ToString();
                    tempHolder[j, 2] = myReader["wk1"].ToString();
                    tempHolder[j, 3] = myReader["wk2"].ToString();
                    tempHolder[j, 4] = myReader["wk3"].ToString();
                    tempHolder[j, 5] = myReader["wk4"].ToString();
                    tempHolder[j, 6] = myReader["wk5"].ToString();
                    tempHolder[j, 7] = myReader["wk6"].ToString();
                    tempHolder[j, 8] = myReader["wk7"].ToString();
                    tempHolder[j, 9] = myReader["wk8"].ToString();
                    tempHolder[j, 10] = myReader["wk9"].ToString();
                    tempHolder[j, 11] = myReader["wk10"].ToString();
                    tempHolder[j, 12] = myReader["wk11"].ToString();
                    tempHolder[j, 13] = myReader["wk12"].ToString();
                    tempHolder[j, 14] = myReader["wk13"].ToString();
                    tempHolder[j, 15] = myReader["wk14"].ToString();
                    tempHolder[j, 16] = myReader["wk15"].ToString();
                    tempHolder[j, 17] = myReader["wk16"].ToString();
                    tempHolder[j, 18] = myReader["wk17"].ToString();
                    tempHolder[j, 19] = myReader["SeasonTotal"].ToString();
                    */
                    tempHolder[j, 0] = myReader["wk1"].ToString();
                    tempHolder[j, 1] = myReader["wk2"].ToString();
                    tempHolder[j, 2] = myReader["wk3"].ToString();
                    tempHolder[j, 3] = myReader["wk4"].ToString();
                    tempHolder[j, 4] = myReader["wk5"].ToString();
                    tempHolder[j, 5] = myReader["wk6"].ToString();
                    tempHolder[j, 6] = myReader["wk7"].ToString();
                    tempHolder[j, 7] = myReader["wk8"].ToString();
                    tempHolder[j, 8] = myReader["wk9"].ToString();
                    tempHolder[j, 9] = myReader["wk10"].ToString();
                    tempHolder[j, 10] = myReader["wk11"].ToString();
                    tempHolder[j, 11] = myReader["wk12"].ToString();
                    tempHolder[j, 12] = myReader["wk13"].ToString();
                    tempHolder[j, 13] = myReader["wk14"].ToString();
                    tempHolder[j, 14] = myReader["wk15"].ToString();
                    tempHolder[j, 15] = myReader["wk16"].ToString();
                    tempHolder[j, 16] = myReader["wk17"].ToString();
                    j++;
                    
                }
                
                int tempResultHolder;
                //Guid tempGuid;
                for(int k=0; k<32; k++)
                {
                    for (int l = 0; l < 17; l++)
                    {
                        if(int.TryParse(tempHolder[k,l], out tempResultHolder))
                        {
                            tempTeams.getTeam(k).setScore(l, tempResultHolder);
                        }
                        else
                        {
                            MessageBox.Show("Invalid int at dataAccessCommon.getTeams\n" + tempHolder[k, l], "Invalid int", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            tempTeams = null;
                        }
                    }
                }

                
            myConn.Close();
            myReader.Close();   

            }
            catch (Exception e)
            {
                MessageBox.Show("Exception at DataAccessCommon.getUser()\n" + e.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            if (myConn != null)
            {
                myConn.Close();
            }
            if (myReader != null)
            {
                myReader.Close();
            }


            if (tempTeams != null)
            {
                return tempTeams;
            }
            else
            {
                return null;
            }

        }


        public void creatAccount(string email, string password, string FirstName, string LastName, string PasswordReset)
        {
            SqlConnection myConn = null;
            SqlDataReader myReader = null;
                     

            try
            {
                myConn = new SqlConnection(myConnectionString);
                SqlCommand createUser = new SqlCommand("CreateUserAccount", myConn);
                createUser.CommandType = CommandType.StoredProcedure;

                SqlParameter userFirstName = createUser.Parameters.Add("@firstName", SqlDbType.NVarChar, 50);
                userFirstName.Direction = ParameterDirection.Input;
                userFirstName.Value = FirstName;

                SqlParameter userLastName = createUser.Parameters.Add("@lastName", SqlDbType.NVarChar, 50);
                userLastName.Direction = ParameterDirection.Input;
                userLastName.Value = LastName;

                SqlParameter userEmail = createUser.Parameters.Add("@email", SqlDbType.NVarChar, 50);
                userEmail.Direction = ParameterDirection.Input;
                userEmail.Value = email;

                SqlParameter userPassword = createUser.Parameters.Add("@password", SqlDbType.NVarChar, 50);
                userPassword.Direction = ParameterDirection.Input;
                userPassword.Value = password;

                SqlParameter userPasswordReset = createUser.Parameters.Add("@passwordResetQuestion", SqlDbType.NVarChar, 50);
                userPasswordReset.Direction = ParameterDirection.Input;
                userPasswordReset.Value = PasswordReset;

                myConn.Open();
                

                myReader = createUser.ExecuteReader();

                
                
                myConn.Close();
                myReader.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Exception at DataAccessCommon.getUser()\n" + e.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            if (myConn != null)
            {
                myConn.Close();
            }
            if (myReader != null)
            {
                myReader.Close();
            }


            //return tempUser;
        }

        public void setUserSelectedTeams(string email, string Team1Name, string Team2Name, string Team3Name, string Team4Name, string Team5Name, string Team6Name, string Team7Name)
        {
            SqlConnection myConn = null;
            SqlDataReader myReader = null;


            try
            {
                myConn = new SqlConnection(myConnectionString);
                SqlCommand setUserSelectedTeams = new SqlCommand("CreateUserAccount", myConn);
                setUserSelectedTeams.CommandType = CommandType.StoredProcedure;

                SqlParameter userEmail = setUserSelectedTeams.Parameters.Add("@email", SqlDbType.NVarChar, 50);
                userEmail.Direction = ParameterDirection.Input;
                userEmail.Value = email;

                SqlParameter Team1 = setUserSelectedTeams.Parameters.Add("@Team1", SqlDbType.NVarChar, 50);
                Team1.Direction = ParameterDirection.Input;
                Team1.Value = Team1Name;

                SqlParameter Team2 = setUserSelectedTeams.Parameters.Add("@Team2", SqlDbType.NVarChar, 50);
                Team2.Direction = ParameterDirection.Input;
                Team2.Value = Team2Name;

                SqlParameter Team3 = setUserSelectedTeams.Parameters.Add("@Team3", SqlDbType.NVarChar, 50);
                Team3.Direction = ParameterDirection.Input;
                Team3.Value = Team3Name;

                SqlParameter Team4 = setUserSelectedTeams.Parameters.Add("@Team4", SqlDbType.NVarChar, 50);
                Team4.Direction = ParameterDirection.Input;
                Team4.Value = Team4Name;

                SqlParameter Team5 = setUserSelectedTeams.Parameters.Add("@Team5", SqlDbType.NVarChar, 50);
                Team5.Direction = ParameterDirection.Input;
                Team5.Value = Team5Name;

                SqlParameter Team6 = setUserSelectedTeams.Parameters.Add("@Team6", SqlDbType.NVarChar, 50);
                Team6.Direction = ParameterDirection.Input;
                Team6.Value = Team6Name;

                SqlParameter Team7 = setUserSelectedTeams.Parameters.Add("@Team7", SqlDbType.NVarChar, 50);
                Team7.Direction = ParameterDirection.Input;
                Team7.Value = Team7Name;

                myConn.Open();


                myReader = setUserSelectedTeams.ExecuteReader();



                myConn.Close();
                myReader.Close();

            }
            catch (Exception e)
            {
                MessageBox.Show("Exception at DataAccessCommon.getUser()\n" + e.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            if (myConn != null)
            {
                myConn.Close();
            }
            if (myReader != null)
            {
                myReader.Close();
            }


            //return tempUser;
        }

    }
}
